package com.palrevo.data.services;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.palrevo.data.dao.DAO;
import com.palrevo.data.dao.DAOFactory;
import com.palrevo.data.dao.DaoRecord;
import com.palrevo.data.dao.DataException;
import com.palrevo.data.models.City;
import com.palrevo.data.models.Program;
import com.palrevo.data.models.ProgramSchedule;
import com.palrevo.data.services.ServiceBase.Columns;
import com.palrevo.data.services.ServiceBase.Tables;
import com.palrevo.utils.Log;

public class ProgramsDBService {

	/**
	 * 
	 */
	private ProgramsDBService() {
	}

	/**
	 * 
	 * @author Mohammed
	 * 
	 */
	private static class SingletonHolder {
		private static ProgramsDBService instance = new ProgramsDBService();
	}

	/**
	 * 
	 * @return ProgramsDBService instance
	 */
	public static ProgramsDBService getInstance() {
		return SingletonHolder.instance;
	}

	/**
	 * 
	 * @return
	 * @throws DataException
	 */
	public List<ProgramSchedule> loadProgramsScheduleTable()
			throws DataException {

		ProgramSchedule schd = null;
		Program prg = null;
		City city = null;
		
		List<ProgramSchedule> schedules = null;

		try {

			DAO dao1 = DAOFactory.getInstance().createDao();

			String sql1 = "SELECT * FROM " + Tables.programs_schedule.name() + " ps , " + Tables.programs.name() + " p, " + Tables.lookup_cities.name() + " c"  
					+ " WHERE ps." + Columns.PROGRAM_ID.name() + " = p." + Columns.ID.name()  + " AND ps." + Columns.CITY_ID.name() + " = c." + Columns.ID.name() 
					+ " AND DATE(ps." + Columns.START_DATE.name() + ") > DATE(NOW())" 
					+ " ORDER BY " + Columns.START_DATE.name()
					+ " ASC LIMIT 15";
			
			dao1.execute(sql1);

			schedules = new ArrayList<ProgramSchedule>();

			for (DaoRecord record1 : dao1.getResult()) {
				schd = new ProgramSchedule();
				schd.setId((Long) record1.getValue(Columns.ID.name()));
				schd.setStartDate((Date)record1.getValue(Columns.START_DATE.name()));
				schd.setEndDate((Date)record1.getValue(Columns.END_DATE.name()));
				
				city = new City();
				city.setId((Long) record1.getValue(Columns.CITY_ID.name()));
				city.setName((String) record1.getValue(Columns.CITY_NAME.name()));
				schd.setCity(city);
				
				prg = new Program();
				prg.setId((Long) record1.getValue(Columns.PROGRAM_ID.name()));
				prg.setName((String) record1.getValue(Columns.PROGRAM_NAME.name()));
				schd.setProgram(prg);
				
				schedules.add(schd);
			}
						
		} catch (Exception ex) {
			Log.error(this, ex.getMessage());
			throw new DataException(ex.getMessage(), ex);
		}

		return schedules;
	}
	
	/**
	 * 
	 * @return
	 * @throws DataException
	 */
	public List<Program> loadAllPrograms()
			throws DataException {

		
		Program prg = null;
		
		List<Program> programs = null;

		try {

			DAO dao1 = DAOFactory.getInstance().createDao();

			String sql1 = "SELECT * FROM " + Tables.programs.name() + " ORDER BY " + Columns.PROGRAM_NAME.name() + " ASC";
			
			dao1.execute(sql1);

			programs = new ArrayList<Program>();

			for (DaoRecord record1 : dao1.getResult()) {
				prg = new Program();
				prg.setId((Long) record1.getValue(Columns.PROGRAM_ID.name()));
				prg.setName((String) record1.getValue(Columns.PROGRAM_NAME.name()));
				programs.add(prg);
			}
						
		} catch (Exception ex) {
			Log.error(this, ex.getMessage());
			throw new DataException(ex.getMessage(), ex);
		}

		return programs;
	}
	
	
	public Program getProgramDetails(Long programId) throws DataException{
		
		Program prg = null;
		
		return prg;
	}


}
